package com.northpool.operator.analysis.utils;

import cn.hutool.core.exceptions.ExceptionUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.BooleanUtil;
import cn.hutool.core.util.StrUtil;
import com.northpool.operator.common.database.DatabaseUtils;
import com.northpool.operator.common.database.bean.DataSourceType;
import com.northpool.operator.param.custom.DatasetParam;
import com.northpool.resources.command.QueryFilter;
import com.northpool.resources.datasource.IDataSource;
import com.northpool.resources.datasource.db.DbDataSource;
import com.northpool.resources.datatable.FieldEncoder;
import com.northpool.resources.datatable.ITable;
import com.northpool.resources.datatable.SpatialInfo;
import com.northpool.resources.datatable.operate.ColumnBean;
import com.northpool.resources.datatable.operate.TableSchemaBean;
import com.northpool.spatial.Constants;
import com.northpool.type.Types;

import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class DatasetUtils {
    /**
     * 获取主键字段在所有字段中的序号
     * @param dataset
     * @return
     */
    public static int getIdIndex(DatasetParam dataset) {
        String dataSourceJson = DatabaseUtils.getDataSourceJson(dataset.getDataSourceId());
        try {
            IDataSource ds = IDataSource.fromJSON(dataSourceJson);
            ITable table = ds.getTable(dataset.getTableName(), FieldEncoder.ORIGIN_FIELD_ENCODER);
            return table.getIdIndex();
        } catch (Exception e) {
            throw new RuntimeException("数据集: " + dataset.toString() + ", 查询失败:" + ExceptionUtil.getSimpleMessage(e));
        }
    }

    /**
     * 获取空间字段在所有字段中的序号
     * @param dataset
     * @return
     */
    public static int getSpatialIndex(DatasetParam dataset) {
        String dataSourceJson = DatabaseUtils.getDataSourceJson(dataset.getDataSourceId());
        try {
            IDataSource ds = IDataSource.fromJSON(dataSourceJson);
            ITable table = ds.getTable(dataset.getTableName(), FieldEncoder.ORIGIN_FIELD_ENCODER);
            return table.getSpatialIndex();
        } catch (Exception e) {
            throw new RuntimeException("数据集: " + dataset.toString() + ", 查询失败:" + ExceptionUtil.getSimpleMessage(e));
        }
    }

    /**
     * 获取表DDL描述实体类
     * @param dataset 数据集
     * @return
     */
    public static TableSchemaBean getTableSchema(DatasetParam dataset) {
        String dataSourceJson = DatabaseUtils.getDataSourceJson(dataset.getDataSourceId());
        try {
            IDataSource ds = IDataSource.fromJSON(dataSourceJson);
            ITable table = ds.getTable(dataset.getTableName(), FieldEncoder.ORIGIN_FIELD_ENCODER);
            return table.getTableOperator().toTableSchemaBean();
        } catch (Exception e) {
            throw new RuntimeException("数据集: " + dataset.toString() + ", 查询失败:" + ExceptionUtil.getSimpleMessage(e));
        }
    }

    /**
     * 获取数据集的空间信息
     *
     * @param dataset 数据集
     * @return 空间信息
     */
    public static SpatialInfo spatialInfo(DatasetParam dataset) {
        String dataSourceJson = DatabaseUtils.getDataSourceJson(dataset.getDataSourceId());
        try {
            IDataSource ds = IDataSource.fromJSON(dataSourceJson);
            ITable table = ds.getTable(dataset.getTableName(), FieldEncoder.ORIGIN_FIELD_ENCODER);
            return table.spatialInfo();
        } catch (Exception e) {
            throw new RuntimeException("数据集: " + dataset.toString() + ", 查询失败:" + ExceptionUtil.getSimpleMessage(e));
        }
    }

    /**
     * 查询数据字段位置
     *
     * @param dataset
     * @return
     */
    public static Map<String, Integer> fieldIndexMap(DatasetParam dataset) {
        String dataSourceJson = DatabaseUtils.getDataSourceJson(dataset.getDataSourceId());
        try {
            IDataSource ds = IDataSource.fromJSON(dataSourceJson);
            ITable table = ds.getTable(dataset.getTableName(), FieldEncoder.ORIGIN_FIELD_ENCODER);
            Map<String, Integer> fieldIndexMap = new LinkedHashMap<>();
            String[] tableFields = table.getFields();
            for (int i = 0; i < tableFields.length; i++) {
                fieldIndexMap.put(tableFields[i], i);
            }
            return fieldIndexMap;
        } catch (Exception e) {
            throw new RuntimeException("数据集: " + dataset.toString() + ", 查询失败:" + ExceptionUtil.getSimpleMessage(e));
        }
    }

    /**
     * 查询数据集中的数据
     *
     * @param dataset 数据集
     * @return 数据列表
     */
    public static List<Object[]> queryDataset(DatasetParam dataset) {
        String dataSourceJson = DatabaseUtils.getDataSourceJson(dataset.getDataSourceId());
        try {
            IDataSource ds = IDataSource.fromJSON(dataSourceJson);
            ITable table = ds.getTable(dataset.getTableName(), FieldEncoder.ORIGIN_FIELD_ENCODER);
            QueryFilter queryFilter = new QueryFilter();
            // 结果字段
            String[] tableFields = table.getFields();
            if (ArrayUtil.isEmpty(dataset.getFields())) {
                queryFilter.setOutputFieldNames(tableFields);
            } else {
                queryFilter.setOutputFieldNames(dataset.getFields());
            }

            // SQL表达式
            if (StrUtil.isNotBlank(dataset.getSqlFilter())) {
                queryFilter.addSQL(dataset.getSqlFilter());
            }
            return table.mapDao().queryArray(queryFilter, 1000);
        } catch (Exception e) {
            throw new RuntimeException("数据集: " + dataset.toString() + ", 查询失败:" + ExceptionUtil.getSimpleMessage(e));
        }
    }

    /**
     * 获取表空间
     *
     * @param dataSourceType 数据源类型
     * @param tableName      表全称
     * @return 表空间
     */
    public static String getSchema(com.northpool.resources.Constants.DATA_SOURCE_TYPE dataSourceType, String tableName) {
        if (DataSourceType.postgreSQL.name().equalsIgnoreCase(dataSourceType.name())
                || DataSourceType.kingbase.name().equalsIgnoreCase(dataSourceType.name())
                || DataSourceType.highgo.name().equalsIgnoreCase(dataSourceType.name())) {
            return StrUtil.blankToDefault(StrUtil.subBefore(tableName, ".", true), "public");
        } else {
            return tableName;
        }
    }

    /**
     * 获取表名称
     *
     * @param dataSourceType 数据源类型
     * @param tableName      表全称
     * @return 表名称
     */
    public static String getTableName(com.northpool.resources.Constants.DATA_SOURCE_TYPE dataSourceType, String tableName) {
        if (DataSourceType.postgreSQL.name().equalsIgnoreCase(dataSourceType.name())
                || DataSourceType.kingbase.name().equalsIgnoreCase(dataSourceType.name())
                || DataSourceType.highgo.name().equalsIgnoreCase(dataSourceType.name())) {
            return StrUtil.subAfter(tableName, ".", true);
        } else {
            return tableName;
        }
    }

    /**
     * 根据模版表创建目标表
     *
     * @param dataSourceId  数据源ID
     * @param templateTable 模板表名称
     * @param targetTable   目标表名称
     * @param geoType       空间字段几何类型
     * @param srid
     */
    public static void createTable(String dataSourceId, String templateTable, String targetTable, Constants.GEO_TYPE geoType, Integer srid) {
        String dataSourceJson = DatabaseUtils.getDataSourceJson(dataSourceId);
        try {
            IDataSource ds = IDataSource.fromJSON(dataSourceJson);
            ITable table = ds.getTable(templateTable, FieldEncoder.ORIGIN_FIELD_ENCODER);
            TableSchemaBean tableSchemaBean = table.getTableOperator().toTableSchemaBean();

            tableSchemaBean.setSchema(getSchema(ds.getDataSourceType(), targetTable));
            tableSchemaBean.setTableName(getTableName(ds.getDataSourceType(), targetTable));
            ColumnBean columnBean = tableSchemaBean.spatialColumn();
            if (columnBean != null && geoType != null && srid != null) {
                columnBean.setGeoType(geoType);
                columnBean.setSrid(srid);
            }
            ds.createTableBySchemaBeanWithoutIndex(tableSchemaBean);
        } catch (Exception e) {
            throw new RuntimeException("数据表: " + targetTable + ", 创建失败:" + ExceptionUtil.getSimpleMessage(e));
        }
    }

    public static com.northpool.resources.Constants.DATA_SOURCE_TYPE dataSourceType(String dataSourceId) {
        String dataSourceJson = DatabaseUtils.getDataSourceJson(dataSourceId);
        try {
            IDataSource ds = IDataSource.fromJSON(dataSourceJson);
            return ds.getDataSourceType();
        } catch (Exception e) {
            throw new RuntimeException("数据源: " + dataSourceJson + "初始化失败: " + ExceptionUtil.getSimpleMessage(e));
        }
    }

    public static boolean tableExists(String dataSourceId, String tableName) {
        String dataSourceJson = DatabaseUtils.getDataSourceJson(dataSourceId);
        try {
            IDataSource ds = IDataSource.fromJSON(dataSourceJson);
            return ds.hasTable(tableName);
        } catch (Exception e) {
            throw new RuntimeException("数据源: " + dataSourceJson + "初始化失败: " + ExceptionUtil.getSimpleMessage(e));
        }
    }

    /**
     * 创建表
     * @param dataSourceId 数据源ID
     * @param tableSchemaBean 表描述实体类
     */
    public static void createTable(String dataSourceId, TableSchemaBean tableSchemaBean) {
        String dataSourceJson = DatabaseUtils.getDataSourceJson(dataSourceId);
        try {
            IDataSource ds = IDataSource.fromJSON(dataSourceJson);
            ds.createTableBySchemaBean(tableSchemaBean);
        } catch (Exception e) {
            throw new RuntimeException("创建数据表失败: " + ExceptionUtil.getSimpleMessage(e));
        }
    }

    /**
     * 基于表达式创建目标表
     *
     * @param fieldExpList 表达式,每一行为一个字段 字段名称,注释,类型,长度,小数点,不是null,是否主键,默认值
     *                     字段1,注释1,String,255,0,false,false,default
     *                     字段2,注释2,String,255,0,false,false,default
     */
    public static TableSchemaBean createTableSchemaBean(com.northpool.resources.Constants.DATA_SOURCE_TYPE dataSourceType, String tableName, String tableRemarks, List<String> fieldExpList) {
        Map<String, ColumnBean> columnMap = new LinkedHashMap<>();
        for (String fieldExp : fieldExpList) {
            List<String> properties = StrUtil.split(fieldExp, ",");
            if (properties.size() != 8) {
                throw new RuntimeException("字段创建表达式错误: " + fieldExp);
            }
            String fieldName = properties.get(0);
            String remarks = properties.get(1);
            String type = properties.get(2);
            String size = properties.get(3);
            String digits = properties.get(4);
            ColumnBean column = new ColumnBean();
            column.setColumnName(fieldName);
            column.setRemarks(remarks);
            column.setType(Types.getTypeByName(type));
            column.setTypeName(type);
            column.setColumnSize(Integer.getInteger(size));
            column.setDigits(Integer.getInteger(digits));
            column.setIsPK(BooleanUtil.toBoolean(properties.get(5)));
            column.setNullable(BooleanUtil.toBoolean(properties.get(6)));
            column.setDefaultValue(properties.get(7));
            columnMap.put(fieldName, column);
        }
        TableSchemaBean tableSchemaBean = new TableSchemaBean();
        tableSchemaBean.setSchema(getSchema(dataSourceType, tableName));
        tableSchemaBean.setTableName(getTableName(dataSourceType, tableName));
        tableSchemaBean.setTableRemarks(tableRemarks);
        tableSchemaBean.setColumnMap(columnMap);

        return tableSchemaBean;
    }

    /**
     * 写入表数据
     *
     * @param dataSourceId 数据源ID
     * @param tableName    表名称
     * @param fields       字段列表
     * @param rows         数据行
     */
    public static void writeRows(String dataSourceId, String tableName, String[] fields, List<Object[]> rows) {
        String dataSourceJson = DatabaseUtils.getDataSourceJson(dataSourceId);
        try {
            IDataSource ds = IDataSource.fromJSON(dataSourceJson);
            if (!(ds instanceof DbDataSource)) {
                throw new RuntimeException("数据源暂不支持数据写入");
            }
            DbDataSource dbDataSource = (DbDataSource) ds;
            ITable table = dbDataSource.getTable(tableName, FieldEncoder.ORIGIN_FIELD_ENCODER);

            String sql = String.format("INSERT INTO %s (\"%s\") VALUES (%s)",
                    StrUtil.format("\"{}\".\"{}\"", getSchema(ds.getDataSourceType(), tableName), getTableName(ds.getDataSourceType(), tableName)),
                    String.join("\", \"", fields),  // 字段名大写
                    String.join(",", Collections.nCopies(fields.length, "?")));
            dbDataSource.batchExecuteSql(sql, rows, table.getTypes(), 100);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}
